DROP TABLE MeterFileGroup
GO

ALTER TABLE Line ADD MaxFaultDistance FLOAT NULL 
GO

ALTER TABLE Line ADD MinFaultDistance FLOAT NULL 
GO

CREATE TABLE MeterConfiguration
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    MeterID INT NOT NULL REFERENCES Meter(ID),
    DiffID INT NULL REFERENCES MeterConfiguration(ID),
    ConfigKey VARCHAR(50) NOT NULL,
    ConfigText VARCHAR(MAX) NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_MeterConfiguration_MeterID
ON MeterConfiguration(MeterID)
GO

CREATE NONCLUSTERED INDEX IX_MeterConfiguration_DiffID
ON MeterConfiguration(DiffID)
GO

CREATE TABLE BreakerRestrike
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
    PhaseID INT NOT NULL REFERENCES Phase(ID),
    Sample INT NOT NULL,
    Timestamp DATETIME2 NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_BreakerRestrike_EventID
ON BreakerRestrike(EventID ASC)
GO

CREATE NONCLUSTERED INDEX IX_BreakerRestrike_Timestamp
ON BreakerRestrike(Timestamp ASC)
GO

INSERT INTO EventType(Name, Description) VALUES ('BreakerOpen', 'Breaker Opening - Nonfault')
GO

CREATE TABLE NoteType(
	ID int not null IDENTITY(1,1) PRIMARY KEY,
	Name varchar(max) not null,
	ReferenceTableName varchar(max) not null,
)
GO

INSERT INTO NoteType (Name, ReferenceTableName) VALUES ('Meter', 'Meter')
GO
INSERT INTO NoteType (Name, ReferenceTableName) VALUES ('Event', 'Event')
GO

CREATE TABLE Note (
	ID int not null IDENTITY(1,1) PRIMARY KEY,
	NoteTypeID int Not NULL REFERENCES NoteType(ID),
	ReferenceTableID INT NOT NULL,
    Note VARCHAR(MAX) NOT NULL,
    UserAccount VARCHAR(MAX) NOT NULL DEFAULT SUSER_NAME(),
    Timestamp DATETIME NOT NULL DEFAULT GETUTCDATE(),
)
GO

CREATE NONCLUSTERED INDEX IX_Note_NoteTypeID_ReferenceTableID  
ON Note(NoteTypeID, ReferenceTableID)
GO

ALTER TABLE LinesToDataPush ADD RemoteLineCreatedByDataPusher BIT NOT NULL DEFAULT(1)
GO

CREATE TABLE FileGroupMeterConfiguration
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL REFERENCES FileGroup(ID),
    MeterConfigurationID INT NOT NULL REFERENCES MeterConfiguration(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupMeterConfiguration_FileGroupID
ON FileGroupMeterConfiguration(FileGroupID)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupMeterConfiguration_MeterConfigurationID
ON FileGroupMeterConfiguration(MeterConfigurationID)
GO

CREATE TABLE RelayAlertSetting
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LineID INT NOT NULL REFERENCES Line(ID),
	TripTime INT NULL,
	PickupTime INT NULL,
	TripCoilCondition FLOAT NULL,
)
GO

CREATE TABLE [dbo].[PQMarkCompanyCustomer](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[PQMarkCompanyID] [int] NOT NULL FOREIGN KEY REFERENCES PQMarkCompany(ID),
	[CustomerID] [int] NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_PQMarkCompanyMeter_MeterID 
ON PQMarkCompanyMeter(MeterID) 
GO

CREATE TABLE RelayPerformance
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EventID INT NOT NULL REFERENCES Event(ID),
	ChannelID INT NOT NULL REFERENCES Channel(ID),
	Imax1 FLOAT NULL,
	Imax2 FLOAT NULL,
    TripInitiate DATETIME2 NULL,
	TripTime INT NULL,
	PickupTime INT NULL,
	TripCoilCondition FLOAT NULL,
)
GO

CREATE VIEW BreakerHistory
AS
SELECT  Line.ID AS LineID,
		RelayPerformance.EventID AS EventID,
        RelayPerformance.Imax1,
		RelayPerformance.Imax2,
		RelayPerformance.TripInitiate,
		RelayPerformance.TripTime / 10 AS TripTime,
		RelayPerformance.PickupTime / 10 AS PickupTime,
		RelayPerformance.TripCoilCondition,
		RelayAlertSetting.TripCoilCondition AS TripCoilConditionAlert,
		RelayAlertSetting.TripTime AS TripTimeAlert,
		RelayAlertSetting.PickupTime AS PickupTimeAlert,
		RelayPerformance.ChannelID AS TripCoilChannelID
FROM    RelayPerformance LEFT OUTER JOIN
        Channel ON RelayPerformance.ChannelID = Channel.ID LEFT OUTER JOIN
        Line ON Channel.LineID = Line.ID LEFT OUTER JOIN 
		RelayAlertSetting ON RelayAlertSetting.LineID = Line.ID
GO

ALTER VIEW LineView
AS
SELECT
    Line.ID,
    Line.AssetKey,
    Line.VoltageKV,
    Line.ThermalRating,
    Line.Length,
    COALESCE(Line.MaxFaultDistance, Line.Length * MaxFaultDistanceMultiplier.Value) MaxFaultDistance,
    COALESCE(Line.MinFaultDistance, Line.Length * MinFaultDistanceMultiplier.Value) MinFaultDistance,
    Line.Description,
    (
        SELECT TOP 1 LineName
        FROM MeterLine
        WHERE LineID = Line.ID
    ) AS TopName,
    LineImpedance.R0,
    LineImpedance.X0,
    LineImpedance.R1,
    LineImpedance.X1,
    LineImpedance.ID AS LineImpedanceID,
	RelayAlertSetting.TripTime,
	RelayAlertSetting.PickupTime,
	RelayAlertSetting.TripCoilCondition,
	RelayAlertSetting.ID AS RelayAlertSettingID
FROM
    Line LEFT OUTER JOIN
    LineImpedance ON Line.ID = LineImpedance.LineID LEFT OUTER JOIN
	RelayAlertSetting ON Line.ID = RelayAlertSetting.LineID CROSS JOIN
    (SELECT COALESCE((SELECT Value FROM Setting WHERE Name = 'FaultLocation.MaxFaultDistanceMultiplier'), 1.05) Value) MaxFaultDistanceMultiplier CROSS JOIN
    (SELECT COALESCE((SELECT Value FROM Setting WHERE Name = 'FaultLocation.MinFaultDistanceMultiplier'), 1.05) Value) MinFaultDistanceMultiplier

GO

ALTER VIEW OpenSEEScalarStatView AS
SELECT
    Event.ID AS EventID,
    MeterLocation.Name AS Station,
    Meter.Name AS Meter,
    Line.AssetKey AS LineKey,
    MeterLine.LineName,
    EventType.Name AS [Event Type],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) / 1000.0, '0.###') AS [File Duration (seconds)],
    FORMAT(DATEDIFF(MILLISECOND, Event.StartTime, Event.EndTime) * System.Frequency / 1000.0, '0.##') AS [File Duration (cycles)],
    FORMAT(FaultSummary.Distance, '0.##') AS [Fault Distance (mi)],
    FORMAT(FaultSummary.DurationSeconds * 1000.0, '0') AS [Fault Duration (ms)],
    FORMAT(FaultSummary.DurationCycles, '0.##') AS [Fault Duration (cycles)],
    FORMAT(Sag.MagnitudePercent, '0.0') AS [Sag Magnitude (%)],
    FORMAT(Sag.MagnitudeVolts, '0') AS [Sag Magnitude (RMS volts)],
    FaultSummary.Algorithm,
    FORMAT(EventStat.VPeak, '0') AS [Voltage Peak (volts)],
    FORMAT(EventStat.VAMax, '0') AS [VA Maximum (RMS volts)],
    FORMAT(EventStat.VBMax, '0') AS [VB Maximum (RMS volts)],
    FORMAT(EventStat.VCMax, '0') AS [VC Maximum (RMS volts)],
    FORMAT(EventStat.VABMax, '0') AS [VAB Maximum (RMS volts)],
    FORMAT(EventStat.VBCMax, '0') AS [VBC Maximum (RMS volts)],
    FORMAT(EventStat.VCAMax, '0') AS [VCA Maximum (RMS volts)],
    FORMAT(EventStat.VAMin, '0') AS [VA Minimum (RMS volts)],
    FORMAT(EventStat.VBMin, '0') AS [VB Minimum (RMS volts)],
    FORMAT(EventStat.VCMin, '0') AS [VC Minimum (RMS volts)],
    FORMAT(EventStat.VABMin, '0') AS [VAB Minimum (RMS volts)],
    FORMAT(EventStat.VBCMin, '0') AS [VBC Minimum (RMS volts)],
    FORMAT(EventStat.VCAMin, '0') AS [VCA Minimum (RMS volts)],
    FORMAT(EventStat.IPeak, '0') AS [Current Peak (Amps)],
    FORMAT(EventStat.IAMax, '0') AS [IA Maximum (RMS Amps)],
    FORMAT(EventStat.IBMax, '0') AS [IB Maximum (RMS Amps)],
    FORMAT(EventStat.ICMax, '0') AS [IC Maximum (RMS Amps)],
    FORMAT(EventStat.IA2t, '0') AS [IA I2t (A2s)],
    FORMAT(EventStat.IB2t, '0') AS [IB I2t (A2s)],
    FORMAT(EventStat.IC2t, '0') AS [IC I2t (A2s)],
    VAN.Mapping AS [VAN Channel],
    VBN.Mapping AS [VBN Channel],
    VCN.Mapping AS [VCN Channel],
    IAN.Mapping AS [IAN Channel],
    IBN.Mapping AS [IBN Channel],
    ICN.Mapping AS [ICN Channel],
    IR.Mapping AS [IR Channel],
	FORMAT(RP.Imax1, '0.000') AS [Lmax 1],
	FORMAT(RP.Imax2, '0.000') AS [Lmax 2],
	FORMAT(RP.TripInitiate,'HH:mm:ss.fff') AS [Trip Initiation],
	(RP.TripTime / 10) AS [Trip Time (microsec)],
	(RP.PickupTime / 10) AS [Pickup Time (microsec)],
	FORMAT(RP.TripCoilCondition, '0.000') AS [Trip Coil Condition (Aps)]
FROM
    Event JOIN
    MeterLine ON
        Event.MeterID = MeterLine.MeterID AND
        Event.LineID = MeterLine.LineID JOIN
    Meter ON Event.MeterID = Meter.ID JOIN
    MeterLocation ON Meter.MeterLocationID = MeterLocation.ID JOIN
    Line ON Event.LineID = Line.ID JOIN
    EventType ON Event.EventTypeID = EventType.ID LEFT OUTER JOIN
    FaultSummary ON
        Event.ID = FaultSummary.EventID AND
        FaultSummary.IsSelectedAlgorithm <> 0 AND
        FaultSummary.FaultNumber = 1 LEFT OUTER JOIN
    EventStat ON Event.ID = EventStat.EventID LEFT OUTER JOIN
    ChannelDetail VAN ON
        Event.MeterID = VAN.MeterID AND
        Event.LineID = VAN.LineID AND
        VAN.MeasurementType = 'Voltage' AND
        VAN.Phase = 'AN' AND
        VAN.MeasurementCharacteristic = 'Instantaneous' AND
        VAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VBN ON
        Event.MeterID = VBN.MeterID AND
        Event.LineID = VBN.LineID AND
        VBN.MeasurementType = 'Voltage' AND
        VBN.Phase = 'BN' AND
        VBN.MeasurementCharacteristic = 'Instantaneous' AND
        VBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail VCN ON
        Event.MeterID = VCN.MeterID AND
        Event.LineID = VCN.LineID AND
        VCN.MeasurementType = 'Voltage' AND
        VCN.Phase = 'CN' AND
        VCN.MeasurementCharacteristic = 'Instantaneous' AND
        VCN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IAN ON
        Event.MeterID = IAN.MeterID AND
        Event.LineID = IAN.LineID AND
        IAN.MeasurementType = 'Current' AND
        IAN.Phase = 'AN' AND
        IAN.MeasurementCharacteristic = 'Instantaneous' AND
        IAN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IBN ON
        Event.MeterID = IBN.MeterID AND
        Event.LineID = IBN.LineID AND
        IBN.MeasurementType = 'Current' AND
        IBN.Phase = 'BN' AND
        IBN.MeasurementCharacteristic = 'Instantaneous' AND
        IBN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail ICN ON
        Event.MeterID = ICN.MeterID AND
        Event.LineID = ICN.LineID AND
        ICN.MeasurementType = 'Current' AND
        ICN.Phase = 'CN' AND
        ICN.MeasurementCharacteristic = 'Instantaneous' AND
        ICN.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
    ChannelDetail IR ON
        Event.MeterID = IR.MeterID AND
        Event.LineID = IR.LineID AND
        IR.MeasurementType = 'Current' AND
        IR.Phase = 'RES' AND
        IR.MeasurementCharacteristic = 'Instantaneous' AND
        IR.SeriesType IN ('Values', 'Instantaneous') LEFT OUTER JOIN
	RelayPerformance RP ON
		Event.ID = RP.EventID AND
		RP.ChannelID IN 
		(
			SELECT ID fROM ChannelDetail RPD WHERE 
				Event.MeterID = RPD.MeterID AND
				Event.LineID = RPD.LineID
		) CROSS JOIN
    (
        SELECT COALESCE(CONVERT(FLOAT,
        (
            SELECT TOP 1 Value
            FROM Setting
            WHERE Name = 'SystemFrequency'
        )), 60.0) AS Frequency
    ) System OUTER APPLY
    (
        SELECT TOP 1
            Disturbance.PerUnitMagnitude * 100 AS MagnitudePercent,
            Disturbance.Magnitude AS MagnitudeVolts
        FROM
            Disturbance JOIN
            EventType ON
                Disturbance.EventTypeID = EventType.ID AND
                EventType.Name = 'Sag' JOIN
            Phase ON
                Disturbance.PhaseID = Phase.ID AND
                Phase.Name = 'Worst'
        WHERE
            Disturbance.EventID = Event.ID AND
            Disturbance.StartTime <= dbo.AdjustDateTime2(FaultSummary.Inception, FaultSummary.DurationSeconds) AND
            Disturbance.EndTime >= FaultSummary.Inception
    ) Sag
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Breakers.MaxCyclesBeforeRestrike', '2.0', '2.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('Breakers.MinCyclesBeforeRestrike', '0.125', '0.125')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.FaultedVoltageThreshold', '0.8', '0.8')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.GroundedFaultVoltageThreshold', '0.001', '0.001')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FaultLocation.MinFaultSegmentCycles', '1.0', '1.0')
GO
INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FolderExclusion', '', '')
GO
INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChart', 'BreakerOpen', 1)
GO
INSERT INTO DashSettings (Name, Value, Enabled) VALUES ('EventsChartColors', 'BreakerOpen,#B245BA', 1)
GO
INSERT INTO MeasurementType(Name, Description) VALUES ('TripCoilCurrent', 'Relay Trip Coil Energization Current')
GO